package com.wxgo.controller;

import java.io.File;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;

import com.jfinal.core.Controller;
import com.jfinal.kit.StrKit;
import com.wxgo.common.model.News;
import com.wxgo.dao.NewsDao;
import com.wxgo.utils.DateUtil;

public class FileController extends Controller {

	private NewsDao newsDao = NewsDao.me;

	public void download() {
		String province = getPara("p", "");
		String outtype = getPara("t", "");
		String newsnameOrUrl = getPara("n", "");
		Long max = getParaToLong("max", 0L);
		Long min = getParaToLong("min", 0L);
		Integer other = getParaToInt("other", 0);
		File file = null;
		if (StrKit.isBlank(province) && StrKit.isBlank(outtype) && StrKit.isBlank(newsnameOrUrl) && max == 0 && min == 0
				&& other == 0) {
			System.out.println("download all file");
			String path = getSession().getServletContext().getRealPath("upload");
			file = new File(path + "/新途网络-新闻媒体报价单-all.xls");
		} else {
			String where = " where 1=1 ";
			List<String> objArr = new ArrayList<String>();
			if (newsnameOrUrl.length() > 0) {
				try {
					newsnameOrUrl = URLDecoder.decode(URLDecoder.decode(newsnameOrUrl, "UTF-8"), "UTF-8");
				} catch (UnsupportedEncodingException e) {
					newsnameOrUrl = "";
				}
				where += " and (newsname like ? or site like ?)";
				objArr.add("%" + newsnameOrUrl + "%");
				objArr.add("%" + newsnameOrUrl + "%");
			}
			if (province.length() > 0) {
				province += "0";
				where += " and pcode in(" + province + ")";
			}

			if (max > min && min >= 0) {
				if (min == 0) {
					where += " and vipprice <= ?";
					objArr.add(max.toString());
				} else {
					where += " and vipprice >= ? and vipprice <=?";
					objArr.add(min.toString());
					objArr.add(max.toString());
				}
			}

			if (outtype.length() > 0) {
				outtype += "0";
				where += " and outtype in (" + outtype + ")";
			}

			if (other > 0) {
				if (other == 1) {
					where += " and  and remark like '%周末%'";
				} else if (other == 2) {
					where += " and remark like '%微商%'";
				} else if (other == 3) {
					where += " and remark like '%医疗%'";
				} else if (other == 4) {
					where += " and remark like '%加盟%'";
				}
			}

			List<News> newsList = newsDao.find(where, objArr.toArray());
			file = ExcelUtil.createExcel(newsList);
		}
		renderFile(file);
	}
}

class ExcelUtil {

	static String INTYPE_WEB = "网页收录";
	static String INTYPE_NEWS = "新闻收录";

	public static File createExcel(List<News> list) {
		// 创建工作簿
		HSSFWorkbook workBook = new HSSFWorkbook();
		// 创建工作表 工作表的名字叫helloWorld
		HSSFSheet sheet = workBook.createSheet("新途网络-新闻媒体报价单");
		sheet.setColumnWidth(0, 8000);
		sheet.setColumnWidth(1, 16000);
		sheet.setColumnWidth(6, 16000);
		// 创建标题行
		HSSFRow row = sheet.createRow(0);
		HSSFCellStyle style = workBook.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);// 居中
		HSSFFont font = workBook.createFont();
		font.setFontName("宋体");
		font.setFontHeightInPoints((short) 14);// 设置字体大小
		font.setBold(true);

		HSSFCell cell = row.createCell(0, CellType.STRING);
		cell.setCellValue("媒体名称");

		cell = row.createCell(1, CellType.STRING);
		cell.setCellValue("案例");

		cell = row.createCell(2, CellType.STRING);
		cell.setCellValue("区域");

		cell = row.createCell(3, CellType.NUMERIC);
		cell.setCellValue("会员价");

		cell = row.createCell(4, CellType.STRING);
		cell.setCellValue("发布类型");

		cell = row.createCell(5, CellType.STRING);
		cell.setCellValue("收录类型");

		cell = row.createCell(6, CellType.STRING);
		cell.setCellValue("备注");
		Integer intype = 1;

		for (int i = 0; i < list.size(); i++) {
			News news = list.get(i);
			// 创建行
			row = sheet.createRow(i + 1);
			// 创建单元格并且添加数据
			row.createCell(0).setCellValue(news.getNewsname());
			row.createCell(1).setCellValue(news.getSite());
			row.createCell(2).setCellValue(news.getProvince());
			row.createCell(3).setCellValue(news.getVipprice());
			row.createCell(4).setCellValue(news.get("type").toString());
			intype = news.getIntype();
			row.createCell(5).setCellValue(intype == 1 ? INTYPE_WEB : INTYPE_NEWS);
			row.createCell(6).setCellValue(news.getRemark());
		}

		try {
			File file = new File("C://xintu/download/新途网络-新闻媒体报价单-" + DateUtil.getStringDateNumber() + ".xls");
			workBook.write(file);
			workBook.close();// 最后记得关闭工作簿
			return file;
		} catch (IOException e) {
			System.out.println("create excel file error");
			e.printStackTrace();
		}
		return null;
	}

}
